package server.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import server.dao.util.DatabaseConstants;
import server.idatasource.IUserDataSource;

public class UserDataSource implements IUserDataSource {

	private Connection	connection;

	private List<List<String>> getResultList(ResultSet resultSet) throws SQLException {

		List<List<String>> resultList = new ArrayList<List<String>>();
		while (resultSet.next()) {
			List<String> result = new ArrayList<String>();
			int i = 1;
			while (i <= 5) {
				result.add(resultSet.getString(i));
				i++;
			}
			resultList.add(result);
		}
		return resultList;
	}

	private Connection getConnection() {

		try {
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
		try {
			connection = DriverManager.getConnection(DatabaseConstants.URL, DatabaseConstants.USERNAME, DatabaseConstants.PASSWORD);
		} catch (Exception e) {
			return null;
		}
		return connection;
	}

	public List<String> handleError() {
		List<String> result = new ArrayList<String>();
		result.add("ERROR");
		return result;
	}

	/*Returns all users in a list. If an error occurs, in the first elements username field 
	 * is returned "ERROR".*/
	@Override
	public List<List<String>> getAllUsers() {

		List<List<String>> resultList = new ArrayList<List<String>>();
		Statement statement = null;
		String query = "";
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			statement = connection.createStatement();
			query = "SELECT * FROM " + DatabaseConstants.USER_TABLE;
			resultSet = statement.executeQuery(query);
			resultList = getResultList(resultSet);
		} catch (Exception e) {
			resultList.add(handleError());
		} finally {
			try {
				connection.close();
				resultSet.close();
				statement.close();
			} catch (Exception e) {
				resultList.add(handleError());
			}
		}
		return resultList;
	}

	/*Returns all information about a user by the argument userName. If an error occurs
	 *the userName field of user is set to "ERROR".*/
	@Override
	public List<String> getUserByUserName(String userName) {

		List<String> result = new ArrayList<String>();
		Statement statement = null;
		String query = "";
		ResultSet resultSet = null;

		try {
			connection = getConnection();
			statement = connection.createStatement();
			query = "SELECT * FROM " + DatabaseConstants.USER_TABLE + " WHERE " + DatabaseConstants.USER_TABLE_USERID + " = '" + userName + "'";

			resultSet = statement.executeQuery(query);
			List<List<String>> resultList = getResultList(resultSet);
			Iterator<List<String>> i = resultList.iterator();
			result = i.next();
		} catch (Exception e) {
			return handleError();
		} finally {
			try {
				connection.close();
				resultSet.close();
				statement.close();
			} catch (SQLException e) {
				return handleError();
			}
		}
		return result;
	}

	/*Checks if exists a user with the name and password given by the arguments userName and 
	 * pass. Returns a string, which is "true" if the user exists, "false" if not exists, and 
	 * "ERROR" if an error occured while proceeding.*/
	@Override
	public String checkUser(String userName, String pass) {

		Statement statement = null;
		String query = "";
		ResultSet resultSet = null;
		String returnValue = "false";

		try {
			connection = getConnection();
			statement = connection.createStatement();
			query = "SELECT * FROM " + DatabaseConstants.USER_TABLE + " WHERE " + DatabaseConstants.USER_TABLE_USERID + " = '" + userName + "' and " + DatabaseConstants.USER_TABLE_PASSWORD + " = '" + pass
					+ "'";

			resultSet = statement.executeQuery(query);
			if (resultSet.next())
				returnValue = "true";
		} catch (Exception e) {
			returnValue = "ERROR1";
		} finally {
			try {
				connection.close();
				resultSet.close();
				statement.close();
			} catch (SQLException e) {
				returnValue = "ERROR";
			}
		}
		return returnValue;
	}

	/*Inserts a new user given in the argument, into the database. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	@Override
	public String insertUser(String username, String name, String password, String email, int groupId) {

		Statement statement = null;
		String command = "";
		String error = "OK";

		try {
			connection = getConnection();
			statement = connection.createStatement();
			command = "INSERT INTO " + DatabaseConstants.USER_TABLE + "VALUES ('" + username + "','" + name + "','" + password + "','" + email + "'," + groupId + ")";
			statement.executeUpdate(command);
		} catch (Exception e) {
			error = e.toString();
		} finally {
			try {
				connection.close();
				statement.close();
			} catch (Exception e) {
				error = e.toString();
			}
		}
		return error;
	}

	/*Deletes a user from database, having the username given in the argument. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	@Override
	public String deleteUser(String userName) {

		Statement statement = null;
		String command = "";
		String error = "OK";

		try {
			connection = getConnection();
			statement = connection.createStatement();
			command = "DELETE FROM " + DatabaseConstants.USER_TABLE + " WHERE " + DatabaseConstants.USER_TABLE_USERID + " = '" + userName + "'";
			statement.executeUpdate(command);
		} catch (Exception e) {
			error = e.toString();
		} finally {
			try {
				connection.close();
				statement.close();
			} catch (Exception e) {
				error = e.toString();
			}
		}
		return error;
	}

	/*Updates the user given in the argument, in the database. Returns a string, with the
	 * value "OK" if the operation was successful, else returns "ERROR".*/
	@Override
	public String updateUser(String username, String name, String password, String email, int groupId) {

		Statement statement = null;
		String command = "";
		String error = "OK";

		try {
			connection = getConnection();
			statement = connection.createStatement();
			command = "UPDATE " + DatabaseConstants.USER_TABLE + " SET " + DatabaseConstants.USER_TABLE_USERID + " = '" + username + "', " + DatabaseConstants.PASSWORD + " = '" + password + "', "
					+ DatabaseConstants.USER_TABLE_EMAIL + " = '" + email + "', " + DatabaseConstants.USER_TABLE_GROUPID + " = '" + groupId + "' WHERE " + DatabaseConstants.USER_TABLE_USERID + " = '"
					+ username + "'";
			//Execute the command
			statement.executeUpdate(command);
		} catch (Exception e) {
			error = e.toString();
		} finally {
			try {
				connection.close();
				statement.close();
			} catch (SQLException e) {
				error = e.toString();
			}
		}
		return error;
	}

}
